﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DreamBuilder.Core.Data;
using CommonModule;
using ePlanet.ResourceRequest.Models;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using System.Data.Common;
using DreamBuilder.Core.AOP.Attribute;
using DreamBuilder.Core.Message;
using DreamBuilder.Common;

namespace ePlanet.ResourceRequest.DataAccess
{
    public class TempUserDAL : DataHelper
    {
        public bool AddNewTempUser(TempUser user)
        {
            bool rtnBool = false;
            if (this.DBHelper.database is OracleDatabase)
            {
                this.InsertData(user);
                rtnBool = true;
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
            return rtnBool;
        }

        public bool UpdateUser(TempUser user)
        {
            bool rtnBool = false;
            if (this.DBHelper.database is OracleDatabase)
            {
                this.InsertData(user);
                rtnBool = true;
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
            return rtnBool;
        }

        /// <summary>
        /// 设置临时用户状态，在P_Auth_User表中添加关联，添加临时用户角色关联
        /// </summary>
        /// <param name="user"></param>
        [Transaction]
        public void AgreeLogin_AddTempUserRelateInP_Auth_User(TempUser user)
        {
            if (this.DBHelper.database is OracleDatabase)
            {
                try
                {
                    user.TEMPUSER[0].STATUSID = (int)TempUserStausEnum.CanLogin;

                    string sqlSatus = string.Format("update TempUser set statusID={0},exameuserID='{1}' where TempUserID='{2}'", user.TEMPUSER[0].STATUSID, user.TEMPUSER[0].EXAMEUSERID, user.TEMPUSER[0].TEMPUSERID);
                    string sqlInsertInP_Auth_User = string.Format(@"declare
  IsLastCount number;
begin
  select count(*)
    into IsLastCount
    from p_auth_user
   where user_id='{0}';
  if IsLastCount = 0 then
     insert into p_auth_user (USER_ID, STATUS_ID, POSITION_ID, SEX_ID, ORG_ID, LOGIN_ID, USER_PWD, USER_NAME, FAX, OFFICE_PHONE, MOBILE, EMAIL, ADDRESS, POSTCODE, CAN_LOGIN, LAST_LOGIN_TIME, THIS_LOGIN_TIME, USER_DOMAIN, ORDER_NUM, REMARK, IDNUMBER, ERPNUMBER, VPNNUMBER, INSURANCENUMBER, OALOGIN_ID, CNPCVERIFICATION, USER_FENLEI, USER_ORG)
values ('{0}', '', '', 'User_Sex--0', '', '{0}', '670B14728AD9902AECBA32E22FA4F6BD', '{1}', '', '', '', '', '', '', 1, '', '', '', 5000, '', '', '', '', '', '', null, '', '');
  end if;
end;", user.TEMPUSER[0].TEMPUSERID, user.TEMPUSER[0].USERNAME);
                    string sqlRole = string.Format("select role_id from p_auth_role where role_name='临时用户'");
                    string tempRoleID = DBHelper.ExecuteScalar(sqlRole).ToString();
                    string sqlUserRole = string.Format(@"declare
  IsLastCount number;
begin
  select count(*)
    into IsLastCount
    from p_auth_user_role
   where user_id='{0}'
   and role_ID='{1}';
  if IsLastCount = 0 then
    insert into p_auth_user_role values('{0}',{1});
     end if;
end;", user.TEMPUSER[0].TEMPUSERID, tempRoleID);
                    DBHelper.ExecuteSql(sqlSatus);
                    DBHelper.ExecuteSql(sqlInsertInP_Auth_User);
                    DBHelper.ExecuteSql(sqlUserRole);
                }
                catch
                {
                    throw;
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
        }

        public void DisAgreeLogin(TempUser user)
        {
            if (this.DBHelper.database is OracleDatabase)
            {
                //DbTransaction trans = DBHelper.GetScopeTransaction();
                try
                {
                    string sql = string.Format("update tempuser set statusID='{0}' where tempuserid='{1}'", (int)TempUserStausEnum.CannotLogin, user.TEMPUSER[0].TEMPUSERID);
                    this.DBHelper.ExecuteSql(sql);
                }
                catch
                {
                    throw;
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
        }
        /// <summary>
        ///通过LoginName查询临时用户
        /// </summary>
        /// <param name="loginName"></param>
        /// <returns></returns>
        public TempUser.TEMPUSERDataTable GetTempUserByLoginName(string loginName)
        {
            TempUser.TEMPUSERDataTable rtnTempUser = new TempUser.TEMPUSERDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                string sql = string.Format(@"select * from tempuser where loginname='{0}'", loginName);
                DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    oDataSet.Tables[0].TableName = rtnTempUser.TableName;
                    rtnTempUser.Merge(oDataSet.Tables[0]);
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
            return rtnTempUser;
        }

        /// <summary>
        ///通过TempUserID查询临时用户
        /// </summary>
        /// <param name="loginName"></param>
        /// <returns></returns>
        public TempUser.TEMPUSERDataTable GetTempUserByUserID(string userID)
        {
            TempUser.TEMPUSERDataTable rtnTempUser = new TempUser.TEMPUSERDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                string sql = string.Format(@"select * from tempuser where TempUserID='{0}'", userID);
                DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    oDataSet.Tables[0].TableName = rtnTempUser.TableName;
                    rtnTempUser.Merge(oDataSet.Tables[0]);
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
            return rtnTempUser;
        }

        /// <summary>
        /// 验证账户密码
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public TempUser.TEMPUSERRow CheckLogin(string loginName, string password)
        {
            TempUser.TEMPUSERRow rtnRow = null;
            if (this.DBHelper.database is OracleDatabase)
            {
                string sql = string.Format(@"select * from tempuser where loginname='{0}' and userpassword='{1}'", loginName, password);
                DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);

                ///有且只有一个
                if (oDataSet != null && oDataSet.Tables.Count > 0 && oDataSet.Tables[0].Rows.Count == 1)
                {
                    TempUser.TEMPUSERDataTable odt = new TempUser.TEMPUSERDataTable();
                    oDataSet.Tables[0].TableName = odt.TableName;
                    odt.Merge(oDataSet.Tables[0]);
                    rtnRow = odt.NewTEMPUSERRow();
                    rtnRow = odt.Rows[0] as TempUser.TEMPUSERRow;
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
            return rtnRow;
        }

        /// <summary>
        /// 获取临时用户列表
        /// </summary>
        /// <param name="statusID"></param>
        /// <param name="beginNum"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable GetTempUserList(TempUserStausEnum statusEnum, string exameUserID)
        {
            DataTable rtnDataTable = null;
            if (this.DBHelper.database is OracleDatabase)
            {
                string sql = string.Empty;

                switch (statusEnum)
                {
                    case TempUserStausEnum.CanLogin:
                    case TempUserStausEnum.CannotLogin:
                        sql = string.Format("select t.*  from tempuser t where t.StatusID={0} and exameUserID='{1}'", (int)statusEnum, exameUserID);
                        break;
                    case TempUserStausEnum.Originale:
                        sql = string.Format("select t.*  from tempuser t where t.StatusID={0}", (int)statusEnum);
                        break;
                    default:
                        throw new Exception("枚举参数TempUserStausEnum有误");
                }
                DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
                if (oDataSet != null && oDataSet.Tables.Count > 0)
                {
                    rtnDataTable = oDataSet.Tables[0];
                }
            }
            else
            {
                throw new Exception("不支持Oracle以外的数据库");
            }
            return rtnDataTable;
        }

        public bool ResetPassword(TempUser.TEMPUSERRow user)
        {
            string sql = string.Format("update tempuser set userpassword ='000000' where tempuserid='{0}'",user.USERPASSWORD);
            this.DBHelper.ExecuteSql(sql);
            return true;
        }

        public bool DelayUser(TempUser.TEMPUSERRow user)
        {
            string sql = string.Format("update tempuser set statusid=0, EXPIRATIONTIME =to_date('{1}','yyyy-mm-dd') where tempuserid='{0}'", user.TEMPUSERID, user.EXPIRATIONTIME.ToString("yyyy-MM-dd"));
            this.DBHelper.ExecuteSql(sql);

            return true;
        }
    }

    public enum TempUserStausEnum
    {
        /// <summary>
        /// 初始状态
        /// </summary>
        Originale = 0,
        /// <summary>
        /// 允许登陆
        /// </summary>
        CanLogin = 1,
        /// <summary>
        /// 不允许登录
        /// </summary>
        CannotLogin = 2
    }
}
